
package com.feib.soeasy.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import org.springframework.util.LinkedCaseInsensitiveMap;

import com.feib.soeasy.dao.GroupDao;
import com.feib.soeasy.dao.Soez5004Dao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.WriteOffPayment;
import com.feib.soeasy.service.Soez5004Service;
import com.feib.soeasy.util.DateUtil;
import com.feib.soeasy.util.UserUtil;


/**
 * @title (#)Soez5004ServiceImpl.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Service("soez5004Service")
public class Soez5004ServiceImpl extends BaseServiceImpl implements Soez5004Service {
private static NumberFormat numberFormat = new DecimalFormat("##,###");
    
    @Resource(name="soez5004Dao")
    private Soez5004Dao soez5004Dao;

    @Resource(name="groupDao")
    private GroupDao groupDao;
    
    /**
     * 暫存目錄
     */
    @Resource(name = "tempDirectory")
    private String tempDirectory;
    
    
  

    /* (non-Javadoc)
     * @see com.feib.soeasy.service.Soez5004Service#queryForList(com.feib.soeasy.model.Group, java.lang.Integer, java.util.Date, java.util.Date)
     */
    @Override
    public Map<String, List<Map<String, Object>> > queryForStatisticsList(Group group, Integer qryDateType, Date dateFrom, Date dateTo) {
        
        if (null != dateFrom)
        {
            // 將時分秒設定在00:00:00
            dateFrom = DateUtil.getBeginOfDateTime(dateFrom);
        }

        if (null != dateTo)
        {
            // 將時分秒設定在23:59:59
            dateTo = DateUtil.getEndOfDateTime(dateTo);
        }
        
        //2011/06/16 jeff Gu  start   修正    業者代號不存在時 查詢錯誤問題

        Group dbGroup = null;
        if (null != group && StringUtils.isNotEmpty(group.getGroupNo()))
        {      
        dbGroup = groupDao.findByGroupNo(group.getGroupNo());
        group.setGroupPk(dbGroup.getGroupPk());
        group.setGroupName(dbGroup.getGroupName());
        }
        
        List<Map<String, Object>> dbRecords = soez5004Dao.getStatisticsForStore((null==dbGroup ?null:dbGroup.getGroupPk()), null, qryDateType, dateFrom, dateTo);
        
        List<Map<String, Object>> dbRecords2 = soez5004Dao.getStatisticsForNotStore((null==dbGroup ?null:dbGroup.getGroupPk()), null, qryDateType, dateFrom, dateTo);
        
      //2011/06/16 jeff Gu  end   修正    業者代號不存在時 查詢錯誤問題
        dbRecords.addAll(dbRecords2);        

        // 無統計資料
        if (null == dbRecords || 0 == dbRecords.size())
            return null;
        
        return this.preProcess(dbRecords);
    }
    
    protected Map<String, List<Map<String, Object>> > preProcess(List<Map<String, Object>> dbRecords)
    {
        
        Map<String, List<Map<String, Object>> > result = new LinkedCaseInsensitiveMap<List<Map<String,Object>>>();
        Map<String, Map<String, Object>> channelSubTotals = new LinkedCaseInsensitiveMap<Map<String, Object>>();
                
        for(Map<String, Object> dbRecord : dbRecords )
        {
            String channel = (String)dbRecord.get("CHANNEL");
            String channelName = (String)dbRecord.get("CHANNEL_NAME");
            //Long commissionCodePk = new Long(String.valueOf(dbRecord.get("COMMISSION_CODE_PK")));
            String commissionCodeName = (String)dbRecord.get("COMMISSION_CODE_NAME");
            Long recordCount = new Long(String.valueOf(dbRecord.get("RECORD_COUNT")));            
            Long bankAmount = new Long(String.valueOf(dbRecord.get("BANK_AMOUNT")));
            
            // 依據通路歸類資料
            List<Map<String, Object>> data = result.get(channel);
            if (null == data)
            {
                data = new ArrayList<Map<String,Object>>();
                result.put(channel, data);                
            }
            
            Map<String, Object> toAddInData = new LinkedCaseInsensitiveMap<Object>();
            toAddInData.put("CHANNEL", channel);
            toAddInData.put("CHANNEL_NAME", channelName);
            toAddInData.put("COMMISSION_CODE_NAME", commissionCodeName);
            toAddInData.put("RECORD_COUNT", recordCount);         
            toAddInData.put("BANK_AMOUNT", bankAmount);   
            data.add(toAddInData);
            
            // 累計
            Map<String, Object> channelSubTotal = channelSubTotals.get(channel);
            if (null == channelSubTotal)
            {
                channelSubTotal = new LinkedCaseInsensitiveMap<Object>();
                channelSubTotal.put("CHANNEL", channel);
                channelSubTotal.put("CHANNEL_NAME", channelName);
                channelSubTotal.put("COMMISSION_CODE_NAME", "小計");
                channelSubTotal.put("RECORD_COUNT", recordCount);           
                channelSubTotal.put("BANK_AMOUNT", bankAmount);
                
                channelSubTotals.put(channel, channelSubTotal);
            }
            else
            {
                channelSubTotal.put("RECORD_COUNT", (Long)channelSubTotal.get("RECORD_COUNT") + recordCount);          
                channelSubTotal.put("BANK_AMOUNT", (Long)channelSubTotal.get("BANK_AMOUNT") + bankAmount);
            }
        }
        
        // 將個別的累計放在個別資料集合的最後一筆資料
        Iterator<String> channelIt = result.keySet().iterator();
        while(channelIt.hasNext())
        {
            String channel = channelIt.next();
            result.get(channel).add(channelSubTotals.get(channel));            
        }
        
        channelSubTotals.clear();
            
        return result;
        
    }
    
    public String doExportStatisticsHtml(Group group, Integer qryType, Date dateFrom, Date dateTo)
    {
        Map<String, List<Map<String, Object>> > dbData = this.queryForStatisticsList(group, qryType, dateFrom, dateTo);
        
        StringBuffer html = new StringBuffer();
      //header row
        String[] titles = {
            "繳款通路", "代收類別", "代收筆數小計", "帳務處理費小計"};
        if (null == dbData || 0 == dbData.size())
        {   //顯示查無資料對話框
            html.append("<div id=\"queryNothingBlockDiv\" style=\"display: none;\">").append("\r\n");
            html.append("查無資料").append("\r\n");
            html.append("</div>").append("\r\n");
            
           
            //顯示表頭
            html.append("<table id=\"listTable\">").append("\r\n");
            html.append("<thead><tr>").append("\r\n");
            
            
            for (int i = 0; i < titles.length; i++) {
                html.append("<th>").append(titles[i]).append("</th>").append("\r\n");
            }
            
            html.append("</tr></thead>").append("\r\n");
            html.append("</tbody></table>").append("\r\n");
            
            html.append("<script type=\"text/javascript\">").append("\r\n");
            html.append("<!--").append("\r\n");
            html.append("$(document).ready(function() {").append("\r\n");
            html.append("$( \"#queryNothingBlockDiv\" ).dialog({  title:'"+UserUtil.getCurrentMenuItemName()+"' ,width: 300, resizable: false, modal: true, buttons : {").append("\r\n");
            html.append("Ok : function() {").append("\r\n");
            html.append("$(this).dialog(\"close\");").append("\r\n");
            html.append("}").append("\r\n");
            html.append("}  });").append("\r\n");
            html.append("});").append("\r\n");
            html.append("//-->").append("\r\n");
            html.append("</script>").append("\r\n");
            return html.toString();
        }
        
        html.append("<table id=\"listTable\">").append("\r\n");
        html.append("<thead><tr>").append("\r\n");
        
        
        for (int i = 0; i < titles.length; i++) {
            html.append("<th>").append(titles[i]).append("</th>").append("\r\n");
        }
        
        html.append("</tr></thead>").append("\r\n");
        html.append("<tbody>").append("\r\n");
        
        long recordCountStoreTotal = 0;
        long bankAmountStoreTotal = 0;
      
        /* 2012/05/21 增加所有通路總計    */
        long recordCountTotal = 0;//代收筆數
        long bankAmountTotal = 0;//帳務處理費
      
        
        String dbDataKeys[] = new String[]{WriteOffPayment.CHANNEL_7_11, WriteOffPayment.CHANNEL_FAMILY_MART
                        , WriteOffPayment.CHANNEL_HILIFE, WriteOffPayment.CHANNEL_OK_MART};
        
        for (String key :dbDataKeys){
            List<Map<String, Object>> channelDatas = dbData.get(key);
            
            if (null != channelDatas &&  0 != channelDatas.size())
            {
                writeHtmlData(html, channelDatas, group);
                
                Map<String, Object> subTotalMap = channelDatas.get(channelDatas.size() - 1); // 於preProcess就有去計算每個通路的小計，並放在最後一筆資料
                recordCountStoreTotal =  recordCountStoreTotal + (Long)subTotalMap.get("RECORD_COUNT");      
                
                bankAmountStoreTotal = bankAmountStoreTotal + (Long)subTotalMap.get("BANK_AMOUNT");
                
            }
            
            
            // 計算完就free
            if (null != channelDatas)
            {
                channelDatas.clear();
                channelDatas.remove(key);
            }
        }
        

        // 超商合計
        html.append("<tr>").append("\r\n");
        /* 2012/05/21 超商合計需縮排    */
        html.append("<td>").append("</td>").append("\r\n").append("<td align=\"left\">超商合計(銷帳日)").append("</td>");        
       // 超商合計-代收筆數小計
        html.append("<td align=\"right\">").append(numberFormat.format(recordCountStoreTotal)).append("</td>").append("\r\n");
        // 超商合計-帳務處理費小計
        html.append("<td align=\"right\">").append(numberFormat.format(bankAmountStoreTotal)).append("</td>").append("\r\n");
        html.append("</tr>").append("\r\n");
        
        
        /* 2012/05/21 增加所有通路總計    */
        recordCountTotal += recordCountStoreTotal;
        bankAmountTotal  += bankAmountStoreTotal;
        // ============= 銀行通路 ===================
        // 累積資料先歸0
        recordCountStoreTotal = 0;
        bankAmountStoreTotal = 0;
        
        // 設定此次要累計與顯示的通路類別
        dbDataKeys = new String[]{WriteOffPayment.CHANNEL_COUNTER, WriteOffPayment.CHANNEL_WEB_ATM
                        , WriteOffPayment.CHANNEL_INTERNET_ATM, WriteOffPayment.CHANNEL_BIO
                        , WriteOffPayment.CHANNEL_XML, WriteOffPayment.CHANNEL_INTERBANK
                        , WriteOffPayment.CHANNEL_POST,WriteOffPayment.CHANNEL_ACH};/* 2012/05/21 顯示ACH通路資料    */
        
        for (String key :dbDataKeys){
            List<Map<String, Object>> channelDatas = dbData.get(key);
            
            if (null != channelDatas &&  0 != channelDatas.size())
            {
                writeHtmlDataForNotStore(html, channelDatas , group);
                
                Map<String, Object> subTotalMap = channelDatas.get(channelDatas.size() - 1); // 於preProcess就有去計算每個通路的小計，並放在最後一筆資料
                recordCountStoreTotal =  recordCountStoreTotal + (Long)subTotalMap.get("RECORD_COUNT");
                
                //2011.12 by Leo
                bankAmountStoreTotal = bankAmountStoreTotal + (Long)subTotalMap.get("BANK_AMOUNT");
                
            }            
            
            // 計算完就free
            if (null != channelDatas)
            {
                channelDatas.clear();
                channelDatas.remove(key);
            }
        }
        
        // 銀行通路合計
        html.append("<tr>").append("\r\n");
        /* 2012/05/21 銀行通路合計需縮排    */
        html.append("<td>").append("</td>").append("\r\n").append("<td align=\"left\">銀行通路合計").append("</td>");
       // 銀行通路合計-代收筆數小計
        html.append("<td align=\"right\">").append(numberFormat.format(recordCountStoreTotal)).append("</td>").append("\r\n");
        // 銀行通路合計-帳務處理費小計
        html.append("<td align=\"right\">").append(numberFormat.format(bankAmountStoreTotal)).append("</td>").append("\r\n");
        html.append("</tr>").append("\r\n");
        
        /* 2012/05/21 增加所有通路總計    */
        recordCountTotal += recordCountStoreTotal;
        bankAmountTotal  += bankAmountStoreTotal;
      
        // 累積資料先歸0
        recordCountStoreTotal = 0;
        bankAmountStoreTotal = 0;

                
        // ============= 終於，算到帳單業者 =============        
        dbDataKeys = new String[]{WriteOffPayment.CHANNEL_SELF};
        
        for (String key :dbDataKeys){
            List<Map<String, Object>> channelDatas = dbData.get(key);
            
            if (null != channelDatas &&  0 != channelDatas.size())
            {
                writeHtmlDataForNotStore(html, channelDatas , group);     
                /* 2012/05/21 將ACH通路加入總數計算   */ 
                Map<String, Object> subTotalMap = channelDatas.get(channelDatas.size() - 1); // 於preProcess就有去計算每個通路的小計，並放在最後一筆資料
                recordCountStoreTotal =  recordCountStoreTotal + (Long)subTotalMap.get("RECORD_COUNT");
                bankAmountStoreTotal = bankAmountStoreTotal + (Long)subTotalMap.get("BANK_AMOUNT");
     
            }            
            
            // 計算完就free
            if (null != channelDatas)
            {
                channelDatas.clear();
                channelDatas.remove(key);
            }
        }
        /* 2012/05/21 增加所有通路總計    */
        recordCountTotal += recordCountStoreTotal;
        bankAmountTotal  += bankAmountStoreTotal;
        
     // 總計
        html.append("<tr>").append("\r\n");
        /* 2012/05/21 增加所有通路總計    */ 
        html.append("<td>").append("</td>").append("\r\n").append("<td align=\"left\">總計").append("</td>");
        // 總計-代收筆數小計
        html.append("<td align=\"right\">").append(numberFormat.format(recordCountTotal)).append("</td>").append("\r\n");
         // 總計-帳務處理費小計
        html.append("<td align=\"right\">").append(numberFormat.format(bankAmountTotal)).append("</td>").append("\r\n");
        html.append("</tr>").append("\r\n");
   
        
        // 下載按鈕
        html.append("<tr>").append("\r\n");
        html.append("<td colspan=\"4\" align=\"center\">")
            .append("<input type=\"button\" id=\"doDownloadExcelFileBtn\" value=\"下載(Excel)\" onclick=\"javascript:doDownloadExcelFileAction();\" />")
            .append(" <input type=\"button\" id=\"printBtn2\" value=\"列印\"  onclick=\"javascript:divPrint('queryResultDiv');\" />") 
            .append("</td>").append("\r\n");
        html.append("</tr>").append("\r\n");
        
        html.append("</tbody></table>").append("\r\n");
        
        return html.toString();
    }
    
    /**
     * @param html
     * @param channelDatas
     */
    private void writeHtmlDataForNotStore(StringBuffer html, List<Map<String, Object>> channelDatas, Group group) {


        // 不用像超商一像分 代收類別，
        // 因為SQL抓出來的時候有分類別，且會經過 preProcess 處理，產生小計
        // 所以，這裡直接抓小計資料就可以了，也就是最後一筆資料
        if (null != channelDatas && 0 != channelDatas.size())
        {
            Map<String, Object> channelData = channelDatas.get(channelDatas.size() - 1);
            //String channel = (String)channelData.get("CHANNEL");
            String channelName = (String)channelData.get("CHANNEL_NAME");                
            String commissionCodeName = (String)channelData.get("COMMISSION_CODE_NAME");
            /* 2012/05/21 銀行通路代收類別強制顯示空白    */
            commissionCodeName = "";
            Long recordCount = (Long)channelData.get("RECORD_COUNT");
            
            Long bankAmount = (Long)channelData.get("BANK_AMOUNT");
            
            html.append("<tr>").append("\r\n");

            // 代收類別
            html.append("<td>").append(channelName).append("</td>").append("\r\n");
            html.append("<td>").append(commissionCodeName).append("</td>").append("\r\n");
            // 代收筆數小計
            html.append("<td align=\"right\">").append(numberFormat.format(recordCount)).append("</td>").append("\r\n");
            // 帳務處理費小計
            html.append("<td align=\"right\">").append(numberFormat.format(bankAmount)).append("</td>").append("\r\n");
            
            html.append("</tr>").append("\r\n");            
        }
        
    }

    /**
     * @param html
     * @param channelDatas
     */
    private void writeHtmlData(StringBuffer html, List<Map<String, Object>> channelDatas, Group group) {
        if (null != channelDatas && 0 != channelDatas.size())
        {
            boolean first = true;
            for(Map<String, Object> channelData : channelDatas)
            {
                String channelName = (String)channelData.get("CHANNEL_NAME");
                String channel = (String)channelData.get("CHANNEL");
                String commissionCodeName = (String)channelData.get("COMMISSION_CODE_NAME");
                Long recordCount = (Long)channelData.get("RECORD_COUNT");
                
                Long bankAmount = (Long)channelData.get("BANK_AMOUNT");
                                
                
                html.append("<tr>").append("\r\n");
                if (first)
                {
                    html.append("<td rowspan=\"").append(channelDatas.size()).append("\">").append(channelName).append("</td>").append("\r\n");
                    first = false;
                }
                

                html.append("<td>").append(commissionCodeName).append("</td>").append("\r\n");
                // 代收筆數小計
                html.append("<td align=\"right\">").append(numberFormat.format(recordCount)).append("</td>").append("\r\n");
                // 帳務處理費小計
                html.append("<td align=\"right\">").append(numberFormat.format(bankAmount)).append("</td>").append("\r\n");
                
                html.append("</tr>").append("\r\n");
            }
           
        }
    }

    public String doExportStatisticsXls(Group group, Integer qryType, Date dateFrom, Date dateTo) throws Exception
    {        
        Map<String, List<Map<String, Object>> > dbData = this.queryForStatisticsList(group, qryType, dateFrom, dateTo);
        
        if (null == dbData)
            return null;
               
        
        SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyy/MM/dd");
        SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
        
        Map<String, CellStyle> styles = createStyles(wb);
        
        Sheet sheet = wb.createSheet("帳務處理統計表");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.setZoom(3, 4);

        // 行數
        int rownum = 0;

        //title row
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints((float)32.25);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("帳務處理統計表");
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1"));
        
        Row blankRow = sheet.createRow(rownum++);
        blankRow.setHeightInPoints((float)18);
        
        //資料期間
        Row conditionRow1 = sheet.createRow(rownum++);
        conditionRow1.setHeightInPoints((float)18);
        Cell conditionCell = conditionRow1.createCell(0);
        conditionCell.setCellValue("帳單業者代號 (統編+序號)：" + group.getGroupNo());
        conditionCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));
        

        
        Cell printDateCell = conditionRow1.createCell(2);
        printDateCell.setCellValue("製表日期/時間:" + dateFormat2.format(Calendar.getInstance().getTime()));
        printDateCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$D$" + rownum));
        

        Row conditionRow2 = sheet.createRow(rownum++);
        conditionRow2.setHeightInPoints((float)25.5);
        Cell groupNameCell = conditionRow2.createCell(0);
        groupNameCell.setCellValue("帳單業者名稱：" + group.getGroupName());
        groupNameCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));
        conditionRow2.setHeightInPoints((float)18);
        
        Cell groupNoCell = conditionRow2.createCell(2);
        groupNoCell.setCellValue("資料期間:" + dateFormat1.format(dateFrom)+ "~" +dateFormat1.format(dateTo));
        groupNoCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$D$" + rownum));
        

        //header row
        String[] titles = {
            "繳款通路", "代收類別", "代收筆數小計", "帳務處理費小計"};
        Row headerRow = sheet.createRow(rownum++);
        headerRow.setHeightInPoints((float) 25.5);
        Cell headerCell;
        for (int i = 0; i < titles.length; i++) {
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(titles[i]);
            headerCell.setCellStyle(styles.get("header"));
        }
        
        
        long recordCountStoreTotal = 0;
        long bankAmountStoreTotal = 0;
        
        /* 2012/05/21 增加所有通路總計    */
        long recordCountTotal = 0;//代收筆數
        long bankAmountTotal = 0;//帳務處理費
      
              
        String dbDataKeys[] = new String[]{WriteOffPayment.CHANNEL_7_11, WriteOffPayment.CHANNEL_FAMILY_MART
                        , WriteOffPayment.CHANNEL_HILIFE, WriteOffPayment.CHANNEL_OK_MART};
        
        for (String key :dbDataKeys){
            List<Map<String, Object>> channelDatas = dbData.get(key);
            
            if (null != channelDatas &&  0 != channelDatas.size())
            {
                writeExcelData(sheet, styles, channelDatas, group);
                
                Map<String, Object> subTotalMap = channelDatas.get(channelDatas.size() - 1); // 於preProcess就有去計算每個通路的小計，並放在最後一筆資料
                recordCountStoreTotal =  recordCountStoreTotal + (Long)subTotalMap.get("RECORD_COUNT");

                bankAmountStoreTotal = bankAmountStoreTotal + (Long)subTotalMap.get("BANK_AMOUNT");
                
            }
            
            
            // 計算完就free
            if (null != channelDatas)
            {
                channelDatas.clear();
                channelDatas.remove(key);
            }
        }
        
        rownum = sheet.getLastRowNum() + 1;
        // 超商合計
        Row dataRow = sheet.createRow(rownum++);
        dataRow.setHeightInPoints((float) 25);
        Cell cell = dataRow.createCell(0);
        cell.setCellValue("超商合計(銷帳日)");
        cell.setCellStyle(styles.get("cell"));
        cell = dataRow.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(styles.get("cell"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));
        
        // 超商合計-代收筆數小計
        cell = dataRow.createCell(2);
        cell.setCellValue(recordCountStoreTotal);
        cell.setCellStyle(styles.get("cell"));
        
        // 超商合計-帳務處理費小計
        cell = dataRow.createCell(3);
        cell.setCellValue(bankAmountStoreTotal);
        cell.setCellStyle(styles.get("cell_number"));
        
        // ============= 銀行通路 ===================
        /* 2012/05/21 增加所有通路總計    */
        recordCountTotal += recordCountStoreTotal;
        bankAmountTotal  += bankAmountStoreTotal;
         // 累積資料先歸0
        recordCountStoreTotal = 0;
        bankAmountStoreTotal = 0;
        
        // 設定此次要累計與顯示的通路類別
        dbDataKeys = new String[]{WriteOffPayment.CHANNEL_COUNTER, WriteOffPayment.CHANNEL_WEB_ATM
                        , WriteOffPayment.CHANNEL_INTERNET_ATM, WriteOffPayment.CHANNEL_BIO
                        , WriteOffPayment.CHANNEL_XML, WriteOffPayment.CHANNEL_INTERBANK
                        , WriteOffPayment.CHANNEL_POST,WriteOffPayment.CHANNEL_ACH};/* 2012/05/21 顯示ACH通路資料    */
        
        for (String key :dbDataKeys){
            List<Map<String, Object>> channelDatas = dbData.get(key);
            
            if (null != channelDatas &&  0 != channelDatas.size())
            {
                writeExcelDataForNotStore(sheet, styles, channelDatas, group);
                
                Map<String, Object> subTotalMap = channelDatas.get(channelDatas.size() - 1); // 於preProcess就有去計算每個通路的小計，並放在最後一筆資料
                recordCountStoreTotal =  recordCountStoreTotal + (Long)subTotalMap.get("RECORD_COUNT");

                bankAmountStoreTotal = bankAmountStoreTotal + (Long)subTotalMap.get("BANK_AMOUNT");                
            }            
            
            // 計算完就free
            if (null != channelDatas)
            {
                channelDatas.clear();
                channelDatas.remove(key);
            }
        }
        
        rownum = sheet.getLastRowNum() + 1;
        // 銀行通路合計
        dataRow = sheet.createRow(rownum++);
        dataRow.setHeightInPoints((float) 25);
        cell = dataRow.createCell(0);
        cell.setCellValue("銀行通路合計");
        cell.setCellStyle(styles.get("cell"));
        cell = dataRow.createCell(1);
        cell.setCellStyle(styles.get("cell"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));
        
        // 銀行通路合計-代收筆數小計
        cell = dataRow.createCell(2);
        cell.setCellValue(recordCountStoreTotal);
        cell.setCellStyle(styles.get("cell"));
        
        // 銀行通路合計-帳務處理費小計
        cell = dataRow.createCell(3);
        cell.setCellValue(bankAmountStoreTotal);
        cell.setCellStyle(styles.get("cell_number"));
        
        /* 2012/05/21 增加所有通路總計    */
        recordCountTotal += recordCountStoreTotal;
        bankAmountTotal  += bankAmountStoreTotal;
        // 累積資料先歸0
        recordCountStoreTotal = 0;
        bankAmountStoreTotal = 0;
        
        // ============= 終於，算到帳單業者 =============        
        dbDataKeys = new String[]{WriteOffPayment.CHANNEL_SELF};
        
        for (String key :dbDataKeys){
            List<Map<String, Object>> channelDatas = dbData.get(key);
            
            if (null != channelDatas &&  0 != channelDatas.size())
            {
                writeExcelDataForNotStore(sheet, styles, channelDatas, group);     
                /* 2012/05/21 計算總計資料    */
                Map<String, Object> subTotalMap = channelDatas.get(channelDatas.size() - 1); // 於preProcess就有去計算每個通路的小計，並放在最後一筆資料
                recordCountStoreTotal =  recordCountStoreTotal + (Long)subTotalMap.get("RECORD_COUNT");   
                bankAmountStoreTotal = bankAmountStoreTotal + (Long)subTotalMap.get("BANK_AMOUNT");
           
            }            
            
            // 計算完就free
            if (null != channelDatas)
            {
                channelDatas.clear();            
                channelDatas.remove(key);
            }
        }
        /* 2012/05/21 增加所有通路總計    */
        recordCountTotal += recordCountStoreTotal;
        bankAmountTotal  += bankAmountStoreTotal;
     

        
        rownum = sheet.getLastRowNum() + 1;
        // 總計
        dataRow = sheet.createRow(rownum++);
        dataRow.setHeightInPoints((float) 25);
        cell = dataRow.createCell(0);
        cell.setCellValue("總計");
        cell.setCellStyle(styles.get("cell"));
        cell = dataRow.createCell(1);
        cell.setCellStyle(styles.get("cell"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));
        
        // 總計-代收筆數小計
        cell = dataRow.createCell(2);
        cell.setCellValue(recordCountTotal);
        cell.setCellStyle(styles.get("cell"));
 
        // 總計-帳務處理費小計
        cell = dataRow.createCell(3);
        cell.setCellValue(bankAmountTotal);
        cell.setCellStyle(styles.get("cell_number"));
   

        int colCount = 0;
        //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題 
        //titles.length == 欄位數
        while (colCount <= titles.length) 
        {
            sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
        }
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        
        String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
        File directory = new File(tempDownloadFilePath);             
        if(!directory.exists())
        {
            FileUtils.forceMkdir(directory);
        }
        
        String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-帳務處理統計表.xls";
        
        FileOutputStream out;
        out = new FileOutputStream(fileFullName);
        wb.write(out);
        out.close();
                
        return fileFullName;
    }
    
    private void writeExcelDataForNotStore(Sheet sheet, Map<String, CellStyle> styles, List<Map<String, Object>> channelDatas , Group group)
    {
        
        // 不用像超商一像分 代收類別，
        // 因為SQL抓出來的時候有分類別，且會經過 preProcess 處理，產生小計
        // 所以，這裡直接抓小計資料就可以了，也就是最後一筆資料
        if (null != channelDatas && 0 != channelDatas.size())
        {
            Map<String, Object> channelData = channelDatas.get(channelDatas.size() - 1);
            Row dataRow = null;
            Cell cell = null;
            int rownum = sheet.getLastRowNum() + 1;
            String cellRangeAddress = null;
            String channelName = (String)channelData.get("CHANNEL_NAME"); 
            String channel = (String)channelData.get("CHANNEL");
            String commissionCodeName = (String)channelData.get("COMMISSION_CODE_NAME");
            /* 2012/05/21 銀行通路代收類別強制顯示空白    */
            commissionCodeName = "";
            Long recordCount = (Long)channelData.get("RECORD_COUNT");
            Long bankAmount = (Long)channelData.get("BANK_AMOUNT");
          
            
            dataRow = sheet.createRow(rownum++);
            if (null == cellRangeAddress)
                cellRangeAddress = "$A$"+rownum;
            dataRow.setHeightInPoints((float) 25);
            cell = dataRow.createCell(0);
            cell.setCellValue(channelName);
            cell.setCellStyle(styles.get("cell"));
            
            // 代收類別
            cell = dataRow.createCell(1);
            cell.setCellValue(commissionCodeName);
            cell.setCellStyle(styles.get("cell"));
            
            // 代收筆數小計
            cell = dataRow.createCell(2);
            cell.setCellValue(recordCount);
            cell.setCellStyle(styles.get("cell"));
            
            // 帳務處理費小計
            cell = dataRow.createCell(3);
            cell.setCellValue(bankAmount);
            cell.setCellStyle(styles.get("cell_number"));
            
            sheet.addMergedRegion(CellRangeAddress.valueOf(cellRangeAddress + ":$A$" + rownum));            
        }
    }
    
    
    private void writeExcelData(Sheet sheet, Map<String, CellStyle> styles, List<Map<String, Object>> channelDatas , Group group)
    {
        if (null != channelDatas && 0 != channelDatas.size())
        {
            Row dataRow = null;
            Cell cell = null;
            int rownum = sheet.getLastRowNum() + 1;
            String cellRangeAddress = null;
            for(Map<String, Object> channelData : channelDatas)
            {
                String channelName = (String)channelData.get("CHANNEL_NAME");
                String channel = (String)channelData.get("CHANNEL");
                String commissionCodeName = (String)channelData.get("COMMISSION_CODE_NAME");
                Long recordCount = (Long)channelData.get("RECORD_COUNT");
                
                Long bankAmount = (Long)channelData.get("BANK_AMOUNT");

                dataRow = sheet.createRow(rownum++);
                if (null == cellRangeAddress)
                    cellRangeAddress = "$A$"+rownum;
                dataRow.setHeightInPoints((float) 25);
                cell = dataRow.createCell(0);
                cell.setCellValue(channelName);
                cell.setCellStyle(styles.get("cell"));
                
                // 代收類別
                cell = dataRow.createCell(1);
                cell.setCellValue(commissionCodeName);
                cell.setCellStyle(styles.get("cell"));
                
                // 代收筆數小計
                cell = dataRow.createCell(2);
                cell.setCellValue(recordCount);
                cell.setCellStyle(styles.get("cell"));
                
                // 帳務處理費小計
                cell = dataRow.createCell(3);
                cell.setCellValue(bankAmount);
                cell.setCellStyle(styles.get("cell_number"));
            }

            sheet.addMergedRegion(CellRangeAddress.valueOf(cellRangeAddress + ":$A$" + rownum));            
        }
    }
    
    /**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontName("標楷體");
        titleFont.setFontHeightInPoints((short)24);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        
        Font title2Font = wb.createFont();
        title2Font.setFontName("標楷體");
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題 
        title2Font.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(title2Font);      
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styles.put("title2", style);

        Font headerFont = wb.createFont();
        headerFont.setFontName("標楷體");
        headerFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(headerFont);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("header", style);

        Font cellFont = wb.createFont();
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell", style);
        
        style = wb.createCellStyle();
        Font cellNumberFont = wb.createFont();
        cellNumberFont.setFontName("Arial");
        cellNumberFont.setFontHeightInPoints((short)14);
        style.setFont(cellNumberFont);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
        styles.put("cell_number", style);
        
        style = wb.createCellStyle();
        Font cellDateFont = wb.createFont();
        cellDateFont.setFontName("Arial");
        cellDateFont.setFontHeightInPoints((short)12);
        style.setFont(cellDateFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
        styles.put("cell_date", style);
        

        cellFont = wb.createFont();
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)18);
        cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell_sub_total", style);
        
        style = wb.createCellStyle();
        cellNumberFont = wb.createFont();
        cellNumberFont.setFontName("Arial");
        cellNumberFont.setFontHeightInPoints((short)14);
        cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(cellNumberFont);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
        styles.put("cell_number_sub_total", style);
        

        

        cellFont = wb.createFont();
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)18);
        cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell_total", style);
        
        style = wb.createCellStyle();
        cellNumberFont = wb.createFont();
        cellNumberFont.setFontName("Arial");
        cellNumberFont.setFontHeightInPoints((short)14);
        cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(cellNumberFont);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
        styles.put("cell_number_total", style);

        return styles;
    }
    

}
